/**
 * The contents of this file are subject to the license and copyright detailed in the LICENSE and NOTICE files at the root of the source tree and available online at
 * http://www.askosi.org/license.html
 */
package be.destin.skos.core;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Level;
import org.apache.log4j.Logger;

import be.destin.rdf.changes.Status;
import be.destin.skos.html.SkosHtmlManager;
import be.destin.util.Util;

public class TabSource {

	/** log4j category */
	private static Logger log = Logger.getLogger(TabSource.class);
	
	protected static void readRelationTable(ConceptScheme aScheme, String rootFileName, String tableNameSuffix,
											boolean local, String column, Method putMatch ) {
		String currSchemePrefix = aScheme.getAbout()+SchemeInterface.separator;
		TextTable tt = new TextTable(aScheme, rootFileName,tableNameSuffix);
		HashMap<String,String> rs;
		while ((rs = tt.readRow())!=null) {
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;
	
			String currParent = getAbout(aScheme, rs, tt, column, false);
			if (currParent == null || currParent.isEmpty() ) continue;
	
			Concept entry;
			entry = aScheme.getConcept(currCode);
			if (entry == null && currCode.startsWith(currSchemePrefix)) {
				entry = aScheme.getConcept(currCode.substring(currSchemePrefix.length()+1));
			}
			if (entry == null) {
				String message = aScheme.getAbout() + SchemeInterface.separator + currCode
				+ " does not exist. Reffered for "+column+" "
				+ currParent;
				log.error(message);
				aScheme.getErrors().add(message);
			} else {

				if (local) {
					if (currParent.startsWith(currSchemePrefix)) {
						currParent = currParent.substring(currSchemePrefix.length());
						if (currParent.isEmpty()) continue;
					}
				} else {
					if (currParent.indexOf(SchemeInterface.separator) < 0) {
						String message = currCode + ": "+column+" \""+currParent+"\"is not prefixed with scheme code?";
						log.error(message);
						entry.addError(message);
						continue;
					}
				}

				String aSort = rs.get("sort");
				if (aSort != null && aSort.length() > 0) {
					try {
						long key = Integer.valueOf(aSort);
						entry.setSortKey(key);
					} catch (NumberFormatException e) {
						log.error(aSort+": sort column must contain a number (long)",e);
					}
				}

				String aStatus = rs.get("status");
				Status currStatus = null;
				if (aStatus != null && !aStatus.isEmpty()) {
					currStatus = Status.valueOf(aStatus);
				}
				String relQualifier = rs.get("qual");

				try {
					putMatch.invoke(entry, currParent, currStatus, relQualifier);
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		// close statement
		tt.close();
	}
	
	protected static void readTriplesTable(ConceptScheme aScheme, String rootFileName, String defRel, String defLanguage, boolean allowNewConcept ) {
		Predicate defRelation = null;
		if (defRel != null && !defRel.isEmpty()) {
			defRelation = Predicate.findInConcept(defRel);
		}
		TextTable tt = new TextTable(aScheme, rootFileName,defLanguage);
		HashMap<String,String> rs;
		int errors = 0;
		while ((rs = tt.readRow())!=null) {
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;
	
			Concept entry;
			entry = aScheme.getConcept(currCode);
			if (allowNewConcept && entry == null) {
				entry = new Concept();
				entry = aScheme.putConcept(currCode, entry);
				String aStatus = rs.get("status");
				if (aStatus != null && !aStatus.isEmpty()) {
					entry.setStatus(aStatus);
				}
			}
			if (entry == null) {
				if (errors == 0) {
					String message = aScheme.getAbout() + SchemeInterface.separator + currCode
					+ " does not exist. Reffered for "+rootFileName+'-'+defLanguage;
					log.error(message);
					aScheme.getErrors().add(message);
				}
				errors++;
			} else {

				String aRel = rs.get("rel");
				if (aRel == null || aRel.isEmpty()) aRel = defRel;

				String relQualifier = rs.get("qual");

				String aLanguage = rs.get("lang");
				if (aLanguage == null || aLanguage.isEmpty()) {
					aLanguage = defLanguage;
				}

				String aStatus = rs.get("status");
				Status currStatus = null;
				if (aStatus != null && !aStatus.isEmpty()) {
					currStatus = Status.valueOf(aStatus);
				}
				
				String aFormat = rs.get("format");
				TermFormat currFormat = null;
				if (aFormat != null && !aFormat.isEmpty()) {
					currFormat = TermFormat.valueOf(aFormat);
				}
				
				String aSource = rs.get("source");

				for (Map.Entry<String, String> aColDef: rs.entrySet()) {
					String aCol = aColDef.getKey();
					if (aCol.startsWith("label")) {
						String currLabel = Util.cleanXmlString(aColDef.getValue());
						if (currLabel.isEmpty()) continue;
						String lang = aCol.substring("label".length());
						if (lang.isEmpty()) lang = aLanguage;
						else {
							if (lang.charAt(0) == '-' || lang.charAt(0) == '_') {
								lang = lang.substring(1);
								if (lang.isEmpty()) lang = aLanguage;
							}
						}
						entry.putPrefLabel(lang, currFormat, currLabel, aSource, currStatus);
					}
					else if (aCol.startsWith("note")) {
						String currLabel = Util.cleanXmlString(aColDef.getValue());
						if (currLabel.isEmpty()) continue;
						String lang = aCol.substring("note".length());
						if (lang.isEmpty()) lang = aLanguage;
						else {
							if (lang.charAt(0) == '-' || lang.charAt(0) == '_') {
								lang = lang.substring(1);
								if (lang.isEmpty()) lang = aLanguage;
							}
						}
						entry.putScopeNote(lang, currFormat, currLabel, currStatus);
					}
					else if (aCol.startsWith("value")) {
						String currLabel = Util.cleanXmlString(aColDef.getValue());
						if (currLabel.isEmpty()) continue;
						String lang = aCol.substring("value".length());
						if (lang.isEmpty()) lang = aLanguage;
						else {
							if (lang.charAt(0) == '-' || lang.charAt(0) == '_') {
								lang = lang.substring(1);
								if (lang.isEmpty()) lang = aLanguage;
							}
						}
						entry.putRelation(aRel, relQualifier, lang, currFormat, currLabel, currStatus);
					}
				}
			}
		}
		// close statement
		tt.close();
		if (errors > 1) {
			String message = aScheme.getAbout() + SchemeInterface.separator + errors
			+ " about codes which does not exist. Reffered for "+rootFileName+'-'+defLanguage;
			log.error(message);
			aScheme.getErrors().add(message);			
		}
	}

	protected static void load(ConceptScheme aScheme, Properties properties, String configFile, Map<String, String> notationsDefinitions) {
		// Should follow a logic similar to SQL. Tables will be in separate
		// files (different suffixes)
		// languages are deducted from the list of the languages of the list
		// names
		// code, name-fr, name-en...,
	
		String rootFileName = configFile;
		if (rootFileName.endsWith(SchemeFactory.configExtension))
			rootFileName = configFile.substring(0,configFile.length()-SchemeFactory.configExtension.length());
		TextTable tt = new TextTable(aScheme, rootFileName,SqlSource.namesTable);
		HashMap<String,String> rs;
	
		while ((rs = tt.readRow())!=null) {
			String currLang = TermList.anyLanguage;
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;
	
			Concept entry = aScheme.findAbout_Concept(currCode);
			if (entry== null) {
				entry = Concept.ensure(aScheme,currCode);
				entry = aScheme.putConcept(currCode, entry);
				String aStatus = rs.get("status");
				if (aStatus != null && !aStatus.isEmpty()) {
					entry.setStatus(aStatus);
				}
			}
			String aLang = rs.get("lang");
			if (aLang != null && aLang.length() > 0) {
				currLang = aLang;
			}
			String aSort = rs.get("sort");
			if (aSort != null && aSort.length() > 0) {
				try {
					long key = Integer.valueOf(aSort);
					entry.setSortKey(key);
				} catch (NumberFormatException e) {
					log.error(aSort+": sort column must contain a number (long)",e);
				}
			}

			Status currStatus = null;
			String aStatus = rs.get("status");
			if (aStatus != null && aStatus.length() > 0) {
				// log.debug(nameLang+"="+aName);
				currStatus = Status.valueOf(aStatus);
			}
			String aFormat = rs.get("format");
			TermFormat currFormat = null;
			if (aFormat != null && !aFormat.isEmpty()) {
				currFormat = TermFormat.valueOf(aFormat);
			}
			
			String currSource = rs.get("source");
			// get current row values
			String aName = Util.cleanXmlString(rs.get("label"));
			if (!aName.isEmpty()) {
				// log.debug(nameLang+"="+aName);
				entry.putPrefLabel(currLang, currFormat, aName, currSource, currStatus);
			}
			// get current row values
			aName = rs.get(SchemeFactory.iconRoot);
			if (aName != null && aName.length() > 0) {
				// log.debug(nameLang+"="+aName);
				entry.putIcon(currLang, aName);
			}
			aName = rs.get("url");
			if (aName != null && aName.length() > 0) {
				// log.debug(nameLang+"="+aName);
				entry.putUrl(currLang, aName);
			}
			aName = Util.cleanXmlString(rs.get("note"));
			if (!aName.isEmpty()) {
				// log.debug(nameLang+"="+aName);
				entry.putScopeNote(currLang, currFormat, aName, currStatus);
			}
			aName = Util.cleanXmlString(rs.get("editorial"));
			if (!aName.isEmpty()) {
				// log.debug(nameLang+"="+aName);
				entry.putEditorialNote(currLang, currFormat, aName, currStatus);
			}

			for (Term nameLang : aScheme.getTitle()) {
				String currLSource = rs.get("source-" + nameLang.getLang());
				if (currLSource == null) currLSource = currSource;
				Status currLStatus = currStatus;
				aStatus = rs.get("status-" + nameLang.getLang());
				if (aStatus != null)
					currLStatus = Status.valueOf(aStatus);

				TermFormat currLFormat = currFormat;
				aFormat = rs.get("format-" + nameLang.getLang());
				if (aFormat != null && !aFormat.isEmpty()) {
					currLFormat = TermFormat.valueOf(aFormat);
				}
				
				String laName = Util.cleanXmlString(rs.get("label-" + nameLang.getLang()));
				if (!laName.isEmpty()) {
					// log.debug(nameLang+"="+aName);
					entry.putPrefLabel(nameLang.getLang(), currLFormat, laName, currLSource, currLStatus);
				}
				laName = rs.get(SchemeFactory.iconRoot+"-" + nameLang.getLang());
				if (laName != null && laName.length() > 0) {
					// log.debug(nameLang+"="+aName);
					entry.putIcon(nameLang.getLang(), laName);
				}
				laName = rs.get("url-" + nameLang.getLang());
				if (laName != null && laName.length() > 0) {
					// log.debug(nameLang+"="+aName);
					entry.putUrl(nameLang.getLang(), laName);
				}
				laName = Util.cleanXmlString(rs.get("note-" + nameLang.getLang()));
				if (!laName.isEmpty()) {
					// log.debug(nameLang+"="+aName);
					entry.putScopeNote(nameLang.getLang(), currLFormat, laName, currLStatus);
				}
				laName = Util.cleanXmlString(rs.get("editorial-" + nameLang.getLang()));
				if (!laName.isEmpty()) {
					// log.debug(nameLang+"="+aName);
					entry.putEditorialNote(nameLang.getLang(), currLFormat, laName, currLStatus);
				}
			}
		}
		tt.close();
	
		tt = new TextTable(aScheme, rootFileName,SqlSource.aliassesTable);
		while ((rs = tt.readRow())!=null) {
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;
	
			String currOrigin = getAbout(aScheme, rs, tt, "alias", false);
			if (currOrigin == null || currOrigin.isEmpty()) continue;
	
			Status currStatus = null;
			String aStatus = rs.get("status");
			if (aStatus != null)
				currStatus = Status.valueOf(aStatus);
			Concept entry = aScheme.getConcept(currCode);
			if (entry == null) {
				log
				.error(aScheme.getAbout() + SchemeInterface.separator + currCode
						+ " does not exist. Referred from "
						+ currOrigin);
			}
			else {
				entry.putAlias(currCode,currStatus);
			}
		}
		tt.close();
	
		tt = new TextTable(aScheme, rootFileName,SqlSource.altTable);
		while ((rs = tt.readRow())!=null) {
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;
	
			Status currStatus = null;
			String aStatus = rs.get("status");
			if (aStatus != null)
				currStatus = Status.valueOf(aStatus);

			TermFormat currFormat = null;
			String aFormat = rs.get("format");
			if (aFormat != null && !aFormat.isEmpty()) {
				currFormat = TermFormat.valueOf(aFormat);
			}
			
			String currLanguage = rs.get("lang");
			if (currLanguage == null)
				currLanguage = TermList.anyLanguage;
			String currSource = rs.get("source");
			String currSynonym = rs.get("alternate");
			if (currSynonym == null) {
				tt.missingField(aScheme, "alternate");
				break;
			} else {
				currSynonym = Util.cleanXmlString(currSynonym);
			}
			Concept entry = aScheme.getConcept(currCode);
			if (entry == null) {
				String message = aScheme.getAbout() + SchemeInterface.separator + currCode
				+ " does not exist. Referred for alternate "
				+ currSynonym + "(" + currLanguage + ")";
				log.error(message);
				aScheme.getErrors().add(message);
			} else {
				if (currSynonym.length() > 0) {
					entry.putAltLabel(currLanguage, currFormat, currSynonym, currSource, currStatus);
				}
				String currOrigin = getAbout(aScheme, rs, tt, "alias", false);
				if (currOrigin != null && !currOrigin.isEmpty()) {
					entry.putAlias(currCode,currStatus);
				}

			}
		}
		tt.close();
	
		tt = new TextTable(aScheme, rootFileName,SqlSource.notesTable);
		while ((rs = tt.readRow())!=null) {
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;
	
			Status currStatus = null;
			String aStatus = rs.get("status");
			if (aStatus != null)
				currStatus = Status.valueOf(aStatus);

			TermFormat currFormat = null;
			String aFormat = rs.get("format");
			if (aFormat != null && !aFormat.isEmpty()) {
				currFormat = TermFormat.valueOf(aFormat);
			}
			
			String currLanguage = rs.get("lang");
			if (currLanguage == null)
				currLanguage = TermList.anyLanguage;

			//String currSource = rs.get("source");
			String aName = rs.get("note");
			if (aName == null) {
				tt.missingField(aScheme, "note");
				break;
			} else {
				aName = Util.cleanXmlString(aName);
			}
			String relQualifier = rs.get("qual");
			Concept entry = aScheme.getConcept(currCode);
			if (entry == null) {
				String message = aScheme.getAbout() + SchemeInterface.separator + currCode
				+ " does not exist. Reffered for note "
				+ aName + "(" + currLanguage + ")";
				log.error(message);
				aScheme.getErrors().add(message);
			} else {
				if (aName.length() > 0) {
					if (relQualifier == null || relQualifier.isEmpty() || "scope".equals(relQualifier)) {
						entry.putScopeNote(currLanguage, currFormat, aName, currStatus);
					} else {
						if ("change".equals(relQualifier)) {
							entry.putChangeNote(currLanguage, currFormat, aName, currStatus);
						} else if ("history".equals(relQualifier)) {
							entry.putHistoryNote(currLanguage, currFormat, aName, currStatus);
						} else if ("example".equals(relQualifier)) {
							entry.putExample(currLanguage, currFormat, aName, currStatus);
						} else if ("definition".equals(relQualifier)) {
							entry.putDefinition(currLanguage, currFormat, aName, currStatus);
						} else if ("editorial".equals(relQualifier)) {
							entry.putEditorialNote(currLanguage, currFormat, aName, currStatus);
						}
					}
				}
				for (Term nameLang : aScheme.getTitle()) {
					Status currLStatus = currStatus;
					aStatus = rs.get("status-" + nameLang.getLang());
					if (aStatus != null)
						currLStatus = Status.valueOf(aStatus);

					TermFormat currLFormat = currFormat;
					aFormat = rs.get("format-" + nameLang.getLang());
					if (aFormat != null && !aFormat.isEmpty()) {
						currLFormat = TermFormat.valueOf(aFormat);
					}

					String laName = Util.cleanXmlString(rs.get("note-" + nameLang.getLang()));
					if (!laName.isEmpty()) {
						// log.debug(nameLang+"="+aName);
						if (relQualifier == null || relQualifier.isEmpty() || "scope".equals(relQualifier)) {
							entry.putScopeNote(nameLang.getLang(), currLFormat, laName, currLStatus);
						} else {
							if ("change".equals(relQualifier)) {
								entry.putChangeNote(nameLang.getLang(), currLFormat, laName, currLStatus);
							} else if ("history".equals(relQualifier)) {
								entry.putHistoryNote(nameLang.getLang(), currLFormat, laName, currLStatus);
							} else if ("example".equals(relQualifier)) {
								entry.putExample(nameLang.getLang(), currLFormat, laName, currLStatus);
							} else if ("definition".equals(relQualifier)) {
								entry.putDefinition(nameLang.getLang(), currLFormat, laName, currLStatus);
							} else if ("editorial".equals(relQualifier)) {
								entry.putEditorialNote(nameLang.getLang(), currLFormat, laName, currLStatus);
							}
						}
					}
				}
			}
		}
		tt.close();
	
		try {
			readRelationTable(aScheme,rootFileName,SqlSource.parentsTable,true,"broader",Concept.class.getMethod("putBroader",String.class,Status.class,String.class));
		} catch (NoSuchMethodException NSM) {
			log.error("broader: unknown relation");
		}
		try {
			readRelationTable(aScheme,rootFileName,SqlSource.relatedTable,true,"related",Concept.class.getMethod("putRelated",String.class,Status.class,String.class));
		} catch (NoSuchMethodException NSM) {
			log.error("related: unknown relation");
		}
		try {
			readRelationTable(aScheme,rootFileName,SqlSource.broadMatchTable,false,"broadmatch",Concept.class.getMethod("putBroadMatch",String.class,Status.class,String.class));
		} catch (NoSuchMethodException NSM) {
			log.error("broadmatch: unknown relation");
		}
		try {
			readRelationTable(aScheme,rootFileName,SqlSource.relatedMatchTable,false,"relatedmatch",Concept.class.getMethod("putRelatedMatch",String.class,Status.class,String.class));
		} catch (NoSuchMethodException NSM) {
			log.error("relatedmatch: unknown relation");
		}
		try {
			readRelationTable(aScheme,rootFileName,SqlSource.exactMatchTable,false,"exactmatch",Concept.class.getMethod("putExactMatch",String.class,Status.class,String.class));
		} catch (NoSuchMethodException NSM) {
			log.error("exactmatch: unknown relation");
		}
		try {
			readRelationTable(aScheme,rootFileName,SqlSource.closeMatchTable,false,"closematch",Concept.class.getMethod("putCloseMatch",String.class,Status.class,String.class));
		} catch (NoSuchMethodException NSM) {
			log.error("closematch: unknown relation");
		}

		tt = new TextTable(aScheme, rootFileName,SqlSource.collectionsTable);
		while ((rs = tt.readRow())!=null) {
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;

			String currCollection = rs.get("collection");
			if (currCollection != null && !currCollection.isEmpty()) {
				Concept entry = aScheme.getConcept(currCode);
				if (entry == null) {
					String message = aScheme.getAbout() + SchemeInterface.separator + currCode
					+ " does not exist. Referred for collection "
					+ currCollection;
					log.error(message);
					aScheme.getErrors().add(message);
				} else {
					entry.putCollection(currCollection);
				}
			}
		}
		tt.close();

		tt = new TextTable(aScheme, rootFileName,SqlSource.usagesTable);
		while ((rs = tt.readRow())!=null) {
			String currCode = getAbout(aScheme, rs, tt, "about", true);
			if (currCode == null) continue;
	
			String currApplication = rs.get("application");
			if (currApplication == null)
				currApplication = SkosHtmlManager.askosiDisplayApplication;
			String currRole = rs.get("role");
			if (currRole == null || currRole.isEmpty()) {
				tt.missingField(aScheme, "role");
				break;
			}
			String currCount = rs.get("count");
			if (currCount == null) {
				currCount="1";
			}
			Concept entry = aScheme.getConcept(currCode);
			if (entry == null) {
				String message = aScheme.getAbout() + SchemeInterface.separator + currCode
				+ " does not exist. Reffered for application/role=count "
				+ currApplication+'/'+currRole + '=' + currCount;
				log.error(message);
				aScheme.getErrors().add(message);
			} else {
				entry.putApplicationUsage(currApplication, currRole, Integer.valueOf(currCount), null, true);
			}
		}
		tt.close();
		if (aScheme.getConcept() != null) {
			for (Concept aConcept: aScheme.getConcept()) {
				aConcept.setUsage(aConcept.getUsage());
			}
		}
	
		String aProp = null;
		if (notationsDefinitions.size() > 0) {
			TermList languages = aScheme.getTitle();
			for (String indexCode : notationsDefinitions.keySet()) {
				NotationScheme subList = (NotationScheme) aScheme.getManagerInstance().getScheme(indexCode);
				for (Term language : languages) {
					aProp = properties.getProperty(SchemeFactory.titleRoot + "." + indexCode
							+ "-" + language);
					if (aProp != null && aProp.length() > 0)
						subList.putTitle(aProp, null,language.getLang(), null/*Status*/);
					aProp = properties.getProperty(SchemeFactory.descriptionRoot + "." + indexCode
							+ "-" + language);
					if (aProp != null && aProp.length() > 0)
						subList.putDescription(aProp, null,language.getLang(), null/*Status*/);
					aProp = SchemeFactory.helpRoot + "." + indexCode
							+ "-" + language;
				    subList.putHelp(Url.getConfigURL(properties, aProp), language.getLang());
				}
	
				tt = new TextTable(aScheme, rootFileName,indexCode);
	
				while ((rs = tt.readRow())!=null) {
					String currCode = getAbout(aScheme, rs, tt, "about", true);
					if (currCode == null) continue;
	
					String currIndex = rs.get("notation");
					if (currIndex == null) {
						tt.missingField(aScheme, "notation");
						break;
					}
	
					Concept entry = aScheme.getConcept(currCode);
					if (entry == null) {
						String message = aScheme.getAbout() + SchemeInterface.separator + currCode
						+ " does not exist. Referred for notation "
						+ currIndex;
						log.error(message);
						aScheme.getErrors().add(message);
					} else if (currIndex.length() > 0)
						entry.putNotation(indexCode, currIndex);
				}
				tt.close();
			}
		}
		if (Level.DEBUG.equals(log.getEffectiveLevel())) {
			StringBuffer result=new StringBuffer();
			for (Concept aConcept: aScheme.getConcept()) {
				result.append(aConcept.toString());
				result.append(" -- ");
			}
			log.debug(result.toString());
		}
	}

	protected static String getAbout(ConceptScheme scheme, HashMap<String,String> rs, TextTable tt, String aboutField, boolean mandatory) {
	
		String currCode = Util.cleanXmlString(rs.get(aboutField));
		if (currCode.isEmpty()) {
			if (mandatory) {
				tt.missingField(scheme, aboutField);
			}
			return null;
		}
	
		currCode = scheme.getCleanAbout(currCode);
		if (currCode.length() == 0) return null;
		return currCode;
	}

}
